package com.infore.maintenance.main.service.impl;


import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.infore.common.annotation.RecordLog;
import com.infore.common.datasource.DBProperties;
import com.infore.common.enums.DatabaseDriverClass;
import com.infore.common.enums.ExecuteSql;
import com.infore.common.exception.MyException;
import com.infore.common.model.entity.TBaseQuerySql;
import com.infore.common.response.ApiListResponse;
import com.infore.common.response.ApiPageResponse;
import com.infore.maintenance.main.dao.TBaseQuerySqlMapper;
import com.infore.maintenance.main.service.CrudHandleService;
import com.infore.maintenance.main.utils.parsesql.SqlAnalysis;
import com.infore.maintenance.main.utils.parsesql.TextSqlNode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.*;

/**
 * <p>
 *  数据增删改查操作  服务实现类
 * </p>
 *
 * @author lijh
 * @since 2023-01-11
 */
@Service
@RecordLog(descript = "数据增删改查操作")
public class CrudHandleServiceImpl extends ServiceImpl<TBaseQuerySqlMapper, TBaseQuerySql> implements CrudHandleService {

    @Autowired
    private DBProperties properties;

    @Autowired
    private TBaseQuerySqlMapper sqlMapper;


    /**
     *
     * @param databaseSchema 库名
     * @param tableName 表名
     * @param columnProperty 表列名与jsonArrayData数据key映射，key为数据表colume名value为jsonArrayData行的key
     * @param jsonArrayData json数据
     * @return
     */
    @Override
    public int insertTableListDynamic(String databaseSchema, String tableName, Map<String, String> columnProperty, String jsonArrayData) {

        // 将HashMap转换为LinkedHashMap
        LinkedHashMap<String,String> linkedColumnProperty= new LinkedHashMap<>();
        columnProperty.forEach((k,v) -> linkedColumnProperty.put(k, v));

        //json字符转对象
        JSONArray jsonArray = JSONUtil.parseArray(jsonArrayData);
        //释放对象的值
        columnProperty = null;
        jsonArrayData=null;

        //LinkedHashMap保存了记录的插入顺序
        List<LinkedHashMap<String,Object>> dataList = new ArrayList<>();
        for (int i = 0; i < jsonArray.size(); i++) {
            LinkedHashMap<String,Object> linkedMap = new LinkedHashMap<String,Object>();
            JSONObject jsonObject = jsonArray.getJSONObject(i);
            for(Map.Entry<String,String>  entry:linkedColumnProperty.entrySet()){
                String property = entry.getValue();
                Object propertyValue = jsonObject.get(property);
                if(propertyValue==null){
                    throw new MyException("500","columnProperty中的value与jsonArrayData数据中的key不匹配",property+"!="+JSONUtil.toJsonStr(jsonObject));
                }
                linkedMap.put(property,propertyValue);
            }
            dataList.add(linkedMap);
        }

        Map<String, Object> sqlMap = new HashMap<>();
        sqlMap.put("tableName",databaseSchema+"."+tableName);
        sqlMap.put("mapColumnProperty",linkedColumnProperty);
        sqlMap.put("data",dataList);
        int rows=0;
        try {
            if(DatabaseDriverClass.MYSQL.getDriverClass().equals(properties.getDataMaintain().getDriverClassName())
            || DatabaseDriverClass.MYSQL5.getDriverClass().equals(properties.getDataMaintain().getDriverClassName())){
                rows = sqlMapper.insertTableListDynamicOfMysql(sqlMap);
            }else if(DatabaseDriverClass.ORACLE.getDriverClass().equals(properties.getDataMaintain().getDriverClassName())){
                rows = sqlMapper.insertTableListDynamicOfOracle(sqlMap);
            }else{
                rows = sqlMapper.insertTableListDynamicOfMysql(sqlMap);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new MyException("500","数据插入异常",e.getMessage());
        }
        return rows;
    }

    /**
     *
     * @param databaseSchema 库名
     * @param tableName 表名
     * @param columnProperty 表列名映射jsonArrayData数据key
     * @param jsonData json数据
     * @return
     */
    @Override
    public int insertTableEntityDynamic(String databaseSchema, String tableName, Map<String, String> columnProperty, String jsonData) {
        // 将HashMap转换为LinkedHashMap
        LinkedHashMap<String,String> linkedColumnProperty = new LinkedHashMap<>();
        columnProperty.forEach((k,v) -> linkedColumnProperty.put(k, v));

        LinkedHashMap<String,Object> dataMap = new LinkedHashMap<>();

        Map<String,Object> sourceDataMap = JSONUtil.toBean(jsonData, Map.class);
        for(Map.Entry<String,String>  entry:linkedColumnProperty.entrySet()){
            String property = entry.getValue();
            Object propertyValue = sourceDataMap.get(property);
            if(propertyValue==null){
                throw new MyException("500","columnProperty中的value与jsonData数据中的key不匹配",property+"!="+JSONUtil.toJsonStr(sourceDataMap));
            }
            dataMap.put(property,propertyValue);
        }

        Map<String, Object> sqlMap = new HashMap<>();
        sqlMap.put("tableName",databaseSchema+"."+tableName);
        sqlMap.put("mapColumnProperty",linkedColumnProperty);
        sqlMap.put("data",dataMap);
        int rows=0;
        try {
            rows = sqlMapper.insertTableEntityDynamic(sqlMap);
        } catch (Exception e) {
            e.printStackTrace();
            throw new MyException("500","数据插入异常",e.getMessage());
        }
        return rows;
    }

    @Override
    public int updateTableEntityDynamic(String databaseSchema, String tableName, Map<String, String> setColumnAndValue, Map<String, String> whereColumnAndValue) {
        Map<String, Object> sqlMap = new HashMap<>();
        sqlMap.put("tableName",databaseSchema+"."+tableName);
        sqlMap.put("setColumnAndValue",setColumnAndValue);
        sqlMap.put("whereColumnAndValue",whereColumnAndValue);
        int rows=0;
        try {
            rows = sqlMapper.updateTableEntityDynamic(sqlMap);
        } catch (Exception e) {
            e.printStackTrace();
            throw new MyException("500","数据更新异常",e.getMessage());
        }
        return rows;
    }

    @Override
    public int deleteTableEntityDynamic(String databaseSchema, String tableName, Map<String, String> whereColumnAndValue) {
        Map<String, Object> sqlMap = new HashMap<>();
        sqlMap.put("tableName",databaseSchema+"."+tableName);
        sqlMap.put("whereColumnAndValue",whereColumnAndValue);
        int rows=0;
        try {
            rows = sqlMapper.deleteTableEntityDynamic(sqlMap);
        } catch (Exception e) {
            e.printStackTrace();
            throw new MyException("500","数据删除异常",e.getMessage());
        }
        return rows;
    }

    /**
     * 执行sql
     * @param type
     * @param sql
     * @return
     */
    @Override
    public int executeBySql(ExecuteSql type, String sql) {
        int n=0;
        if(ExecuteSql.INSERT.equals(type)){
            SqlAnalysis.preventSqlInjection(sql, ExecuteSql.INSERT);
            try {
                n = sqlMapper.insertBySql(sql);
            } catch (Exception e) {
                throw new MyException("500","SQL执行异常",e.toString());
            }
        }else if(ExecuteSql.UPDATE.equals(type)){
            SqlAnalysis.preventSqlInjection(sql, ExecuteSql.UPDATE);
            try {
                n = sqlMapper.updateBySql(sql);
            } catch (Exception e) {
                throw new MyException("500","SQL执行异常",e.toString());
            }
        }else if(ExecuteSql.DELETE.equals(type)){
            SqlAnalysis.preventSqlInjection(sql, ExecuteSql.DELETE);
            try {
                n = sqlMapper.deleteBySql(sql);
            } catch (Exception e) {
                throw new MyException("500","SQL执行异常",e.toString());
            }
        }
        return n;
    }
    @Override
    public int executeBySql(ExecuteSql type, String sql,Map<String, Object> map) {
        String  executeSqlUp= sql.trim().toUpperCase();
        int n=0;
        if(ExecuteSql.UPDATE.equals(type)){
            if(executeSqlUp.indexOf("UPDATE") != 0){
                throw new MyException("500","执行sql只能是以UPDATE开头的语句");
            }
            Map<String, Object> parameters = new HashMap<>();
            String executeSql =  TextSqlNode.parseSqlParamenters(sql,map,parameters);
            try {
                n = sqlMapper.updateBySqlAndMap(executeSql,parameters);
            } catch (Exception e) {
                throw new MyException("500","SQL执行异常",e.toString());
            }
        }else if(ExecuteSql.DELETE.equals(type)){
            if(executeSqlUp.indexOf("DELETE") != 0){
                throw new MyException("500","执行sql只能是以DELETE开头的语句");
            }
            Map<String, Object> parameters = new HashMap<>();
            String executeSql =  TextSqlNode.parseSqlParamenters(sql,map,parameters);
            try {
                n = sqlMapper.deleteBySqlAndMap(executeSql,parameters);
            } catch (Exception e) {
                throw new MyException("500","SQL执行异常",e.toString());
            }
        }
        return n;
    }

    @Override
    public ApiListResponse<Map<String, Object>> queryBySql(String sql) {
        String  executeSqlUp= sql.trim().toUpperCase();
        if(executeSqlUp.indexOf("SELECT") != 0){
            throw new MyException("500","执行sql只能是以SELECT开头的语句");
        }
        List<Map<String, Object>> list=null;
        try {
            list = sqlMapper.queryBySql(sql);
        } catch (Exception e) {
            throw new MyException("500","SQL执行异常",e.toString());
        }
        return ApiListResponse.ok(list);

    }

    @Override
    public ApiPageResponse<Map<String, Object>>  queryPageBySql(String sql,int pageNum,int limit) {
        String  executeSqlUp= sql.trim().toUpperCase();
        if(executeSqlUp.indexOf("SELECT") != 0){
            throw new MyException("500","执行sql只能是以SELECT开头的语句");
        }
        Page<Object> page = null;
        List<Map<String, Object>> list=null;
        try {
            page = PageHelper.startPage(pageNum, limit);
            list = sqlMapper.queryBySql(sql);
        } catch (Exception e) {
            throw new MyException("500","SQL执行异常",e.toString());
        }


        return ApiPageResponse.ok(list,page);

    }


}
